from flask import Flask, request, jsonify
from flask_cors import CORS
from conn import connect_to_oracle
import traceback
from datetime import datetime

app = Flask(__name__)
CORS(app)

def convert_value(value):
    if value is None:
        return ""
    elif isinstance(value, datetime):
        return value.strftime('%Y-%m-%d %H:%M:%S')
    return str(value)

def transform_photo_urls(photo_string, bayonet_id, warn_type=None, photo_position=None):
    urls = [s.strip() for s in str(photo_string).split(";") if s.strip()]
    result_urls = []

    for s in urls:
        if bayonet_id == "1":  # 杭申线
            if s.startswith("D:/photo"):
                result_urls.append(s.replace("D:/photo", "http://10.135.10.194/checkPhoto"))
            else:
                result_urls.append(s.replace("/data/dataShareFolder", "https://ghzx.jtw.sh.gov.cn/ghjgWebService/supervise"))
        elif bayonet_id == "2":  # 长湖申线
            if s.startswith("D:/photo"):
                result_urls.append(s.replace("D:/photo", "http://10.0.80.195/checkPhoto"))
            else:
                result_urls.append(s.replace("/data/dataShareFolder", "https://ghzx.jtw.sh.gov.cn/ghjgWebService/supervise"))
        else:  # 其他卡口
            if s.startswith("http://intra.huihangtech.com:4092/api"):
                result_urls.append(s.replace("http://intra.huihangtech.com:4092/api", "https://ghzx.jtw.sh.gov.cn/api"))
            else:
                result_urls.append(s.replace("/data/dataShareFolder", "https://ghzx.jtw.sh.gov.cn/ghjgWebService/supervise"))

    if warn_type == "货物未覆盖" and photo_position:
        extra_url = f"https://ghzx.jtw.sh.gov.cn/ghjgWebService/supervise/bayonetPhoto/analysedPicture/{bayonet_id}{photo_position}"
        result_urls.append(extra_url)

    return result_urls

@app.route('/api/warning', methods=['POST'])
def get_boat_info():
    try:
        data = request.get_json()
        print("收到请求数据：", data)

        boat_name = data.get("boatName")
        start_time = data.get("startTime")
        end_time = data.get("endTime")

        if not boat_name or not start_time or not end_time:
            return jsonify({"error": "参数缺失，需包含 boatname、starttime、endtime"}), 400

        sql = """
        SELECT T.*
        FROM (
            SELECT TO_CHAR(warn.WARNCODE) warnCode,
                   warn.WARNING_TIME warnTime,
                   TO_CHAR(warn.BOAT_NAME) boatName,
                   TO_CHAR(type.NAME) warnType,
                   info.PASSTIME passTime,
                   info.BAYONET_ID bayonetId,
                   sys.name bayonetName,
                   video.PHOTONAME AS photoName,
                   NULL AS photoPosition,
                   CASE 
                       WHEN warn.warning_message_status = 1 THEN '已发送'
                       ELSE '未发送'
                   END AS msgSendStatus
            FROM bayonet_bussiness.boat_warning warn
            LEFT JOIN bayonet_dynamic.DATAFUSION info ON info.CODE = warn.EVENTCODE
            LEFT JOIN bayonet_bussiness.zd_warning_type type ON warn.WARNING_TYPE = type.id
            LEFT JOIN ghjg_basics.mvw_boat_info mvw ON info.BOAT_NAME = mvw.SHIP_NAME_CN
            LEFT JOIN BAYONET_BASICS.SYS_BAYONET sys ON sys.id = info.BAYONET_ID
            LEFT JOIN bayonet_bussiness.message_send_record m ON m.warning_code = warn.warncode
            LEFT JOIN BAYONET_DYNAMIC.VIDEO_ANALYSIS video ON video.EVENTCODE = info.CODE
            WHERE info.BAYONET_ID IS NOT NULL
              AND mvw.SHIP_TYPE_NAME IS NOT NULL

            UNION

            SELECT TO_CHAR(osc.id) warnCode,
                   osc.create_time warnTime,
                   TO_CHAR(info.boat_name) boatName,
                   DECODE(osc.type, 1, '国旗未悬挂', 2, '货物未覆盖') warnType,
                   info.passtime passTime,
                   info.BAYONET_ID bayonetId,
                   sys.name bayonetName,
                   NVL(video.PHOTONAME, '') || NVL(coverage.COLUMN1, '') AS photoName,
                   coverage.COLUMN1 AS photoPosition,
                   DECODE(osc.case_message_status, 0, '未发送', '已发送') msgSendStatus
            FROM bayonet_bussiness.off_site_case osc
            LEFT JOIN bayonet_dynamic.datafusion info ON osc.event_code = info.code
            LEFT JOIN BAYONET_BASICS.SYS_BAYONET sys ON sys.id = info.BAYONET_ID
            LEFT JOIN BAYONET_DYNAMIC.VIDEO_ANALYSIS video ON video.EVENTCODE = info.CODE
            LEFT JOIN BAYONET_DYNAMIC.COVERAGE_ANALYSIS coverage ON coverage.EVENT_CODE = info.CODE
        ) T
        WHERE T.boatName = ?
          AND T.warnTime BETWEEN TO_DATE(?, 'YYYY-MM-DD HH24:MI:SS')
                             AND TO_DATE(?, 'YYYY-MM-DD HH24:MI:SS')
        ORDER BY T.warnTime DESC
        """

        conn = connect_to_oracle()
        curs = conn.cursor()
        curs.execute(sql, [boat_name, start_time, end_time])
        columns = [str(desc[0]).lower() for desc in curs.description]
        rows = curs.fetchall()

        result = []
        for row in rows:
            row_dict = {}
            for i, col in enumerate(columns):
                value = convert_value(row[i])
                if col == "photoname":
                    photo_name = value
                    bayonet_id = str(row[columns.index("bayonetid")])
                    warn_type = row[columns.index("warntype")] if "warntype" in columns else None
                    photo_position = row[columns.index("photoposition")] if "photoposition" in columns else None
                    row_dict[col] = transform_photo_urls(photo_name, bayonet_id, warn_type, photo_position)
                else:
                    row_dict[col] = value
            result.append(row_dict)

        curs.close()
        conn.close()

        return jsonify({"result": result})

    except Exception as e:
        traceback.print_exc()
        return jsonify({"error": "服务器内部错误", "details": str(e)}), 500

# if __name__ == '__main__':
#     app.run(debug=True)
